/**
 * 战技指标表迁移脚本
 * 添加层级关系字段，支持树形结构
 */

const { pool } = require('../config/db');

async function migrateWeaponSpecifications() {
  const connection = await pool.getConnection();
  
  try {
    console.log('开始迁移 weapon_specifications 表...');
    
    await connection.beginTransaction();
    
    // 1. 检查是否已经存在新字段
    const [columns] = await connection.query(`
      SHOW COLUMNS FROM weapon_specifications LIKE 'parent_id'
    `);
    
    if (columns.length > 0) {
      console.log('✓ 表结构已是最新版本，无需迁移');
      await connection.commit();
      return;
    }
    
    // 2. 备份原有数据
    console.log('- 备份原有数据...');
    const [oldData] = await connection.query('SELECT * FROM weapon_specifications');
    console.log(`  备份了 ${oldData.length} 条记录`);
    
    // 3. 删除旧表
    console.log('- 删除旧表...');
    await connection.query('DROP TABLE IF EXISTS weapon_specifications');
    
    // 4. 创建新表结构
    console.log('- 创建新表结构...');
    await connection.query(`
      CREATE TABLE weapon_specifications (
        id INT PRIMARY KEY AUTO_INCREMENT COMMENT '主键ID',
        raw_weapon_id INT NOT NULL COMMENT '原始武器装备表ID',
        
        -- 层级关系
        parent_id INT DEFAULT NULL COMMENT '父级指标ID（NULL表示顶级）',
        level INT DEFAULT 1 COMMENT '层级深度（1=顶级，2=二级，以此类推）',
        node_type ENUM('category', 'indicator') DEFAULT 'indicator' COMMENT '节点类型：category=分类/目录节点，indicator=具体指标',
        
        -- 战技指标信息
        indicator_chinese VARCHAR(255) COMMENT '指标中文名称',
        indicator_english VARCHAR(255) COMMENT '指标英文名称',
        indicator_value VARCHAR(500) COMMENT '指标值（node_type=indicator时有值）',
        category_name VARCHAR(255) COMMENT '分类名称（node_type=category时使用）',
        
        -- 排序字段
        sort_order INT DEFAULT 0 COMMENT '排序顺序',
        
        -- 时间戳
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
        
        INDEX idx_raw_weapon_id (raw_weapon_id),
        INDEX idx_parent_id (parent_id),
        INDEX idx_level (level),
        INDEX idx_node_type (node_type),
        FOREIGN KEY (raw_weapon_id) REFERENCES raw_weapons(id) ON DELETE CASCADE,
        FOREIGN KEY (parent_id) REFERENCES weapon_specifications(id) ON DELETE CASCADE
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='装备战技指标表（树形结构）'
    `);
    
    // 5. 迁移旧数据（将旧数据作为顶级指标导入）
    if (oldData.length > 0) {
      console.log('- 迁移旧数据...');
      for (const row of oldData) {
        await connection.query(`
          INSERT INTO weapon_specifications 
          (raw_weapon_id, parent_id, level, node_type, indicator_chinese, indicator_english, indicator_value, sort_order, created_at, updated_at)
          VALUES (?, NULL, 1, 'indicator', ?, ?, ?, ?, ?, ?)
        `, [
          row.raw_weapon_id,
          row.indicator_chinese,
          row.indicator_english,
          row.indicator_value,
          row.sort_order || 0,
          row.created_at,
          row.updated_at
        ]);
      }
      console.log(`  ✓ 迁移了 ${oldData.length} 条记录`);
    }
    
    await connection.commit();
    console.log('✅ 迁移完成！');
    
  } catch (error) {
    await connection.rollback();
    console.error('❌ 迁移失败:', error.message);
    throw error;
  } finally {
    connection.release();
  }
}

// 执行迁移
if (require.main === module) {
  migrateWeaponSpecifications()
    .then(() => {
      console.log('迁移脚本执行完成');
      process.exit(0);
    })
    .catch((error) => {
      console.error('迁移脚本执行失败:', error);
      process.exit(1);
    });
}

module.exports = { migrateWeaponSpecifications };

